package zozopo.warehouse.operations;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import zozopo.database.BusiDatabaseManager;
import zozopo.database.SqlMachine;
import zozopo.operations.comm.OperatingService;

public class AftersaleserviceOS extends OperatingService{
	
	
	public AftersaleserviceOS(Connection conn){
		super(conn);
	}
	
	public AftersaleserviceOS(){
		this.databaseConnection = new BusiDatabaseManager();
		this.conn = databaseConnection.getConnection();
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<AftersaleserviceTB> selectListBySql(String sql){
		List<AftersaleserviceTB> list = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		//Flow flow = new Flow(conn);
		//Map flowMap = flow.getFlowStateMapByKind(FlowCode.HOUSEWARE_RECEIPTLIST);
		if(resultSet != null){
				try {
					list = new ArrayList<AftersaleserviceTB>();
					while(resultSet.next()){
						AftersaleserviceTB obj = new AftersaleserviceTB();
						
						obj.setId_aftersaleservice(resultSet.getInt(AftersaleserviceTB.ID_AFTERSALESERVICE));
						
						obj.setCustomerid_aftersaleservice(resultSet.getString(AftersaleserviceTB.CUSTOMERID_AFTERSALESERVICE));
						obj.setCustomerinfo_aftersaleservice(resultSet.getString(AftersaleserviceTB.CUSTOMERINFO_AFTERSALESERVICE));
						obj.setDate_aftersaleservice(resultSet.getString(AftersaleserviceTB.DATE_AFTERSALESERVICE));
						obj.setDepartment_aftersaleservice(resultSet.getString(AftersaleserviceTB.DEPARTMENT_AFTERSALESERVICE));
						obj.setGoodsinfo_aftersaleservice(resultSet.getString(AftersaleserviceTB.GOODSINFO_AFTERSALESERVICE));
						obj.setReceiptlistid_aftersaleservice(resultSet.getInt(AftersaleserviceTB.RECEIPTLISTID_AFTERSALESERVICE));
						obj.setRemark_aftersaleservice(resultSet.getString(AftersaleserviceTB.REMARK_AFTERSALESERVICE));
						obj.setShipno_aftersaleservice(resultSet.getString(AftersaleserviceTB.SHIPNO_AFTERSALESERVICE));
						obj.setShipprice_aftersaleservice(resultSet.getDouble(AftersaleserviceTB.SHIPPRICE_AFTERSALESERVICE));
						obj.setState_aftersaleservice(resultSet.getString(AftersaleserviceTB.STATE_AFTERSALESERVICE));
						obj.setReceiveshipno_aftersaleservice(resultSet.getString(AftersaleserviceTB.RECEIVESHIPNO_AFTERSALESERVICE));
						obj.setReceiveshipprice_aftersaleservice(resultSet.getDouble(AftersaleserviceTB.RECEIVESHIPPRICE_AFTERSALESERVICE));
						obj.setUseremail_aftersaleservice(resultSet.getString(AftersaleserviceTB.USEREMAIL_AFTERSALESERVICE));
						obj.setUsername_aftersaleservice(resultSet.getString(AftersaleserviceTB.USERNAME_AFTERSALESERVICE));
						
						obj.setProblemdescription_aftersaleservice(resultSet.getString(AftersaleserviceTB.PROBLEMDESCRIPTION_AFTERSALESERVICE));
						obj.setConfirmproblemdescription_aftersaleservice(resultSet.getString(AftersaleserviceTB.CONFIRMPROBLEMPROBLEMDESCRIPTION_AFTERSALESERVICE));
						list.add(obj);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return list;
	
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean insert(AftersaleserviceTB aftersaleservice){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> insertMap = new HashMap<String, Object>();
		
		insertMap.put(AftersaleserviceTB.CUSTOMERID_AFTERSALESERVICE, aftersaleservice.getCustomerid_aftersaleservice());
		
		insertMap.put(AftersaleserviceTB.CUSTOMERINFO_AFTERSALESERVICE, aftersaleservice.getCustomerinfo_aftersaleservice());
		
		//insertMap.put(AftersaleserviceTB.DATE_AFTERSALESERVICE, aftersaleservice.getCustomerinfo_aftersaleservice());
		insertMap.put(AftersaleserviceTB.DEPARTMENT_AFTERSALESERVICE, aftersaleservice.getDepartment_aftersaleservice());
		//insertMap.put(AftersaleserviceTB.DEPARTMENTID_AFTERSALESERVICE, aftersaleservice.getCustomerinfo_aftersaleservice());
		insertMap.put(AftersaleserviceTB.GOODSINFO_AFTERSALESERVICE, aftersaleservice.getGoodsinfo_aftersaleservice());
		//insertMap.put(AftersaleserviceTB.GOODSNUMBER_AFTERSALESERVICE, aftersaleservice.getCustomerinfo_aftersaleservice());
		insertMap.put(AftersaleserviceTB.PROBLEMDESCRIPTION_AFTERSALESERVICE, aftersaleservice.getProblemdescription_aftersaleservice());
		insertMap.put(AftersaleserviceTB.RECEIPTLISTID_AFTERSALESERVICE, aftersaleservice.getReceiptlistid_aftersaleservice());
		insertMap.put(AftersaleserviceTB.REMARK_AFTERSALESERVICE, aftersaleservice.getRemark_aftersaleservice());
		
		insertMap.put(AftersaleserviceTB.RECEIVESHIPNO_AFTERSALESERVICE, aftersaleservice.getReceiveshipno_aftersaleservice());
		//insertMap.put(AftersaleserviceTB.SHIPNO_AFTERSALESERVICE, aftersaleservice.getShipno_aftersaleservice());
		insertMap.put(AftersaleserviceTB.SHIPPRICE_AFTERSALESERVICE, aftersaleservice.getShipprice_aftersaleservice());
		
		insertMap.put(AftersaleserviceTB.USEREMAIL_AFTERSALESERVICE, aftersaleservice.getUseremail_aftersaleservice());
		insertMap.put(AftersaleserviceTB.USERNAME_AFTERSALESERVICE, aftersaleservice.getUsername_aftersaleservice());
		
		return sqlMachine.insertRow(AftersaleserviceTB.TABLE_NAME, insertMap);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public int getMaxIdByEmail(String email){
		//String sql = "select * from "+ReleaseTB.TABLE_NAME+" order by  "+ReleaseTB.RELEASE_DATE+" desc limit "+beginPageNum+","+pageSize; 
		SqlMachine sqlMachine = new SqlMachine(conn);
		int maxId = sqlMachine.getMaxId(AftersaleserviceTB.ID_AFTERSALESERVICE, AftersaleserviceTB.TABLE_NAME, AftersaleserviceTB.USEREMAIL_AFTERSALESERVICE, email);
		return maxId;
	}
	
	public AftersaleserviceTB get(int id){
		String sql = "SELECT * FROM " + AftersaleserviceTB.TABLE_NAME + " where " + AftersaleserviceTB.ID_AFTERSALESERVICE + "=" + id;
		AftersaleserviceTB obj = new AftersaleserviceTB();
		List<AftersaleserviceTB> list = selectListBySql(sql);
		obj = list.get(0);
		return obj;
	}
	
	public AftersaleserviceTB getByCustomerid(String customerid){
		String sql = "SELECT * FROM " + AftersaleserviceTB.TABLE_NAME + " where " + AftersaleserviceTB.CUSTOMERID_AFTERSALESERVICE + "='" + customerid +"'";
		AftersaleserviceTB obj = new AftersaleserviceTB();
		List<AftersaleserviceTB> list = selectListBySql(sql);
		if(list.size() == 0){
			return null;
		}else{
			obj = list.get(0);
		}
		
		return obj;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<AftersaleserviceTB> selectAll(){
		String sql = "SELECT * FROM " + AftersaleserviceTB.TABLE_NAME;
		List<AftersaleserviceTB> list =  selectListBySql(sql);
		
		return list;
	}
	
	public List<AftersaleserviceTB> selectAll(int beginPageNum,int pageSize){
		
		List<AftersaleserviceTB> list = null;
		String sql = "SELECT * FROM " + AftersaleserviceTB.TABLE_NAME  
		+ " ORDER BY " + AftersaleserviceTB.ID_AFTERSALESERVICE + " DESC "
		+ " limit "+beginPageNum+","+pageSize; 
		list = selectListBySql(sql);
		
		return list;
	}
	
	public List<AftersaleserviceTB> selectByReceiptlistid(int receiptlistid){
		
		List<AftersaleserviceTB> list = null;
		String sql = "SELECT * FROM " + AftersaleserviceTB.TABLE_NAME  
		+ " where " + AftersaleserviceTB.RECEIPTLISTID_AFTERSALESERVICE + "=" + receiptlistid
		
		+ " ORDER BY " + AftersaleserviceTB.ID_AFTERSALESERVICE + " DESC ";
		
		list = selectListBySql(sql);
		
		return list;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNum(){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + AftersaleserviceTB.TABLE_NAME ;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<AftersaleserviceTB> selectBySearchStr(String searchStr,int beginPageNum,int pageSize){
		String sql = "select * from "+AftersaleserviceTB.TABLE_NAME+" WHERE  "
		+AftersaleserviceTB.ID_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.CUSTOMERID_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.CUSTOMERINFO_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.DATE_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		
		+" OR "+AftersaleserviceTB.GOODSINFO_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.RECEIVESHIPNO_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.RECEIPTLISTID_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.REMARK_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.SHIPNO_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.USERNAME_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"

		+ " ORDER BY " + AftersaleserviceTB.DATE_AFTERSALESERVICE + " DESC "
		
		+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		List<AftersaleserviceTB> receiptlistList = selectListBySql(sql);
		
		return receiptlistList;
	}
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + AftersaleserviceTB.TABLE_NAME +" WHERE  "
		+AftersaleserviceTB.ID_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.CUSTOMERID_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.CUSTOMERINFO_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.DATE_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		
		+" OR "+AftersaleserviceTB.GOODSINFO_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.RECEIVESHIPNO_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.RECEIPTLISTID_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.REMARK_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.SHIPNO_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'"
		+" OR "+AftersaleserviceTB.USERNAME_AFTERSALESERVICE+" LIKE '%"+searchStr+"%'";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean upadte(String shipno,double shipPrice,int id){
		
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(AftersaleserviceTB.SHIPNO_AFTERSALESERVICE, shipno);
		updateMap.put(AftersaleserviceTB.SHIPPRICE_AFTERSALESERVICE, shipPrice);
		//insertMap.put(supplierTB.DATE_supplier, goods.getUnit_goods());
		
		return sqlMachine.updateRow(AftersaleserviceTB.TABLE_NAME, updateMap, AftersaleserviceTB.ID_AFTERSALESERVICE, id);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean upadteReceive(String shipno,String goodsinfo,int id){
		
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(AftersaleserviceTB.RECEIVESHIPNO_AFTERSALESERVICE, shipno);
		updateMap.put(AftersaleserviceTB.GOODSINFO_AFTERSALESERVICE, goodsinfo);
		//insertMap.put(supplierTB.DATE_supplier, goods.getUnit_goods());
		
		return sqlMachine.updateRow(AftersaleserviceTB.TABLE_NAME, updateMap, AftersaleserviceTB.ID_AFTERSALESERVICE, id);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean upadteConfimQuestion(String question,int id){
		
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		
		updateMap.put(AftersaleserviceTB.CONFIRMPROBLEMPROBLEMDESCRIPTION_AFTERSALESERVICE, question);
		//insertMap.put(supplierTB.DATE_supplier, goods.getUnit_goods());
		
		return sqlMachine.updateRow(AftersaleserviceTB.TABLE_NAME, updateMap, AftersaleserviceTB.ID_AFTERSALESERVICE, id);
	}
	
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean upadte(AftersaleserviceTB aftersaleservice){
		SqlMachine sqlMachine = new SqlMachine(conn);
		
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(AftersaleserviceTB.CUSTOMERID_AFTERSALESERVICE, aftersaleservice.getCustomerid_aftersaleservice());
		updateMap.put(AftersaleserviceTB.CUSTOMERINFO_AFTERSALESERVICE, aftersaleservice.getCustomerinfo_aftersaleservice());
		
		updateMap.put(AftersaleserviceTB.GOODSINFO_AFTERSALESERVICE, aftersaleservice.getGoodsinfo_aftersaleservice());
		updateMap.put(AftersaleserviceTB.RECEIVESHIPNO_AFTERSALESERVICE, aftersaleservice.getReceiveshipno_aftersaleservice());
		updateMap.put(AftersaleserviceTB.SHIPNO_AFTERSALESERVICE, aftersaleservice.getShipno_aftersaleservice());
		updateMap.put(AftersaleserviceTB.REMARK_AFTERSALESERVICE, aftersaleservice.getRemark_aftersaleservice());
		updateMap.put(AftersaleserviceTB.RECEIPTLISTID_AFTERSALESERVICE, aftersaleservice.getReceiptlistid_aftersaleservice());
		updateMap.put(AftersaleserviceTB.PROBLEMDESCRIPTION_AFTERSALESERVICE, aftersaleservice.getProblemdescription_aftersaleservice());
		
		return sqlMachine.updateRow(AftersaleserviceTB.TABLE_NAME, updateMap, AftersaleserviceTB.ID_AFTERSALESERVICE, aftersaleservice.getId_aftersaleservice());
	}

	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<AftersaleserviceTB> selectByFlowCode(String flowCode,String kindCode,int beginPageNum,int pageSize){
		String sql = "SELECT * FROM "+AftersaleserviceTB.TABLE_NAME+" LEFT JOIN recordlist_flow " +
				" ON recordlist_flow.kindid_recordlist = " + AftersaleserviceTB.ID_AFTERSALESERVICE +
				" AND recordlist_flow.flowflag_recordlist='1' " +
				" WHERE  recordlist_flow.code_recordlist='"+flowCode+"'" +
				" AND recordlist_flow.kind_recordlist='"+kindCode+"' " +
				" AND " + AftersaleserviceTB.ISCANCEL_AFTERSALESERVICE+ "=false " +
		        " limit "+beginPageNum+","+pageSize; 
		
		List<AftersaleserviceTB> receiptlistList = selectListBySql(sql);
		return receiptlistList;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForFlowCode(String flowCode,String kindCode){
		
		int rowNum = 0;
		
		String sql = "SELECT count(*) as rowNum FROM "+AftersaleserviceTB.TABLE_NAME+" LEFT JOIN recordlist_flow " +
					" ON recordlist_flow.kindid_recordlist = "+ AftersaleserviceTB.ID_AFTERSALESERVICE +
					" AND recordlist_flow.flowflag_recordlist='1' " +
					" AND recordlist_flow.kind_recordlist='"+kindCode+"' " +
					" WHERE  recordlist_flow.code_recordlist='"+flowCode+"'"+
					" AND " + AftersaleserviceTB.ISCANCEL_AFTERSALESERVICE+ "=false ";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
}
